import time
from datetime import timedelta

import numpy as np

import 小区
from 单用户中断时长工作.Input import *
from 文件数据.pppoe import *

olt_cell_path = "D:\\中断输入\\OLTPON口对小区.csv"


def ip_pon(row):
    split = row.split('/')
    if len(split) == 3:
        return '-' + split[1] + '-' + split[2]
    return ''


def detail():
    print(f"pppoe日期：{new_date}")
    print(time.ctime())
    interrupt = pd.read_csv(getInterruptDetail(cur_date),
                            usecols=['deviceId', 'interruptionCnt', 'interruptionTime'],
                            index_col=False)
    interrupt.rename(columns={
        'deviceId': 'SN',
        'interruptionCnt': '中断次数',
        'interruptionTime': '中断时长'
    }, inplace=True)

    cpe = pd.read_csv(get_cpe_by_date(new_date), usecols=[0, 2], names=['SN', '光猫型号'])
    interrupt = interrupt.merge(cpe, on="SN", how='left')
    del cpe
    print("cpe 完成")
    print(time.ctime())
    pppoe = pd.read_csv(get_pppoe_by_date(new_date), usecols=[0, 4, 5, 6, 7, 10]
                        , names=['宽带账号', 'OLTIP', 'OLTPORT', 'onuid', 'SN', 'pppoe上线时间'],
                        dtype=str)
    interrupt = interrupt.merge(pppoe, on='SN', how='left')
    del pppoe
    # 按照 SN 分组，先按时间降序排序，再使用 drop_duplicates 保留每组的第一条记录
    interrupt = interrupt.sort_values(by=['SN', 'pppoe上线时间'], ascending=[True, False]).drop_duplicates(
        subset='SN')
    print("pppoe 完成")
    print(time.ctime())

    interrupt = handler_cellID(interrupt)
    print("小区id 完成")
    print(time.ctime())
    interrupt = handler_olt_info(interrupt)
    print("olt业务配置查询 完成")
    print(time.ctime())
    interrupt = handler_cell(interrupt)
    print("小区 完成")
    print(time.ctime())
    interrupt['日期'] = date
    interrupt['周'] = week
    header = ['SN', '中断次数', '中断时长', '日期', '周', '宽带账号', 'OLTIP', '网元名称', 'OLTPORT', 'OLTIP+OLTPORT',
              'POS标准名称',
              'onuid', '小区ID', '分公司', '小区名称', 'pppoe上线时间', '光猫型号', '社区归属网格']
    interrupt = interrupt[header]
    interrupt.to_excel(get_detail_path(cur_date), index=False)


def handler_cellID(interrupt):
    olt_cell = pd.read_csv(olt_cell_path, dtype=str)
    # 删除pon为空的行
    olt_cell = olt_cell.dropna(subset=['OLTIP+OLTPORT'])
    interrupt['OLTPORT'] = interrupt['OLTPORT'].astype(str)
    interrupt['OLTIP+OLTPORT'] = interrupt['OLTIP'] + interrupt['OLTPORT'].apply(ip_pon)
    interrupt = interrupt.merge(olt_cell, on='OLTIP+OLTPORT', how='left')
    # 可能有一个pon对应对个小区，删掉保留最后一行
    interrupt = interrupt.drop_duplicates(subset=['SN'], keep='last')
    return interrupt


def handler_cell(interrupt):
    cell = pd.read_csv(小区.get_path(), usecols=['网元内部编码', '中文名称', '社区归属网格', '分公司'], dtype=str,
                       encoding='gbk')
    cell.rename(columns={
        '网元内部编码': '小区ID',
        '中文名称': '小区名称'
    }, inplace=True)
    interrupt = interrupt.merge(cell, on='小区ID', how='left')
    return interrupt


def handler_olt_info(interrupt):
    olt_info = pd.read_csv(olt_path, usecols=['网管侧网元名称', '管理IP', '网元名称', '设备供应商'], dtype=str)
    olt_info['网管侧网元名称'] = olt_info['网管侧网元名称'].fillna(olt_info['网元名称'])
    olt_info.drop(columns=['网元名称'], inplace=True)
    olt_info.rename(columns={
        '网管侧网元名称': '网元名称',
        '管理IP': 'OLTIP',
    }, inplace=True)
    interrupt = interrupt.merge(olt_info, on='OLTIP', how='left')
    x_values = np.where(interrupt['设备供应商'] == '华为', '0', '1')
    y_values = interrupt['OLTPORT'].str.split('/').str[-2:].str.join('-')
    interrupt['POS标准名称'] = interrupt['网元名称'] + '-在用-1-' + x_values + '-' + y_values + '-在用'
    return interrupt


def four():
    interrupt = pd.read_excel(get_detail_path(cur_date))
    interrupt['分公司'] = interrupt['分公司'].fillna('全市')
    result = interrupt.groupby('分公司').agg(
        中断总次数=('中断次数', 'sum'),
        中断总时长秒=('中断时长', 'sum'),
        中断用户数=('分公司', 'count')
    ).reset_index()
    result = get_company().merge(result, on='分公司', how='left')
    user_num = pd.read_excel("D:\\中断输入\\活跃用户数新.xlsx", usecols=['分公司', '活跃用户数'])
    result = result.merge(user_num, on='分公司', how='left')

    # 对除去第一列进行求和，并将结果累加到最后一行
    result.iloc[-1, 1:] = result.iloc[:, 1:].sum()
    result.loc[result['分公司'] == '全市', '活跃用户数'] = user_num.iloc[-1, 1]
    result['中断总时长分'] = result['中断总时长秒'] / 60
    result['单用户中断时长'] = result['中断总时长分'] / result['活跃用户数']
    result['中断用户平均修复时长'] = result['中断总时长分'] / result['中断用户数'] / 60
    result['用户平均中断率'] = result['中断用户数'] / result['活跃用户数'] * 100
    result['用户平均中断次数'] = result['中断总次数'] / result['中断用户数']
    result = result[
        ['分公司', '活跃用户数', '中断总时长秒', '中断总时长分', '中断用户数', '中断总次数', '单用户中断时长',
         '中断用户平均修复时长'
            , '用户平均中断率', '用户平均中断次数'
         ]]
    result.to_excel(get_four_path(cur_date), index=False)

    print(result)


'''

更新活跃用户
'''
if __name__ == '__main__':
    # 11的时候是11
    week = '23'
    a = 15
    b = a + 1
    for i in range(a, b):
        # 将字符串转换为 datetime 对象

        date = f'2025-06-{util.get_day(i)}'

        cur_date = datetime.strptime(date, "%Y-%m-%d")
        # 向后退两天
        new_date = cur_date + timedelta(days=2)
        print(date)
        detail()
        four()
